
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 07/13/2010 16:55:44
-- Generated from EDMX file: C:\orderentrysystem\OrderEntryManagement.Entities\OrderEntryModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [OrderEntryManagement];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_Asset_AssetType]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Assets] DROP CONSTRAINT [FK_Asset_AssetType];
GO
IF OBJECT_ID(N'[dbo].[FK_Order_Asset]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Orders] DROP CONSTRAINT [FK_Order_Asset];
GO
IF OBJECT_ID(N'[dbo].[FK_Order_Customer]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Orders] DROP CONSTRAINT [FK_Order_Customer];
GO
IF OBJECT_ID(N'[dbo].[FK_Order_OrderType]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Orders] DROP CONSTRAINT [FK_Order_OrderType];
GO
IF OBJECT_ID(N'[dbo].[FK_Order_ProductionStatus]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Orders] DROP CONSTRAINT [FK_Order_ProductionStatus];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Assets]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Assets];
GO
IF OBJECT_ID(N'[dbo].[AssetTypes]', 'U') IS NOT NULL
    DROP TABLE [dbo].[AssetTypes];
GO
IF OBJECT_ID(N'[dbo].[Customers]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Customers];
GO
IF OBJECT_ID(N'[dbo].[Orders]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Orders];
GO
IF OBJECT_ID(N'[dbo].[OrderTypes]', 'U') IS NOT NULL
    DROP TABLE [dbo].[OrderTypes];
GO
IF OBJECT_ID(N'[dbo].[ProductionStatus]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ProductionStatus];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Assets'
CREATE TABLE [dbo].[Assets] (
    [AssetID] int IDENTITY(1,1) NOT NULL,
    [AssetTypeID] int  NOT NULL,
    [AssetName] varchar(50)  NOT NULL
);
GO

-- Creating table 'AssetTypes'
CREATE TABLE [dbo].[AssetTypes] (
    [AssetTypeID] int IDENTITY(1,1) NOT NULL,
    [AssetTypeName] varchar(50)  NOT NULL
);
GO

-- Creating table 'Customers'
CREATE TABLE [dbo].[Customers] (
    [CustomerID] int IDENTITY(1,1) NOT NULL,
    [CustomerName] varchar(500)  NOT NULL,
    [Address] varchar(1000)  NULL
);
GO

-- Creating table 'Orders'
CREATE TABLE [dbo].[Orders] (
    [OrderID] int IDENTITY(1,1) NOT NULL,
    [CustomerID] int  NOT NULL,
    [AssetID] int  NOT NULL,
    [PublishDate] datetime  NOT NULL,
    [ProductionStatusID] int  NOT NULL,
    [AdSize] int  NULL,
    [OrderTypeID] int  NOT NULL
);
GO

-- Creating table 'OrderTypes'
CREATE TABLE [dbo].[OrderTypes] (
    [OrderTypeID] int IDENTITY(1,1) NOT NULL,
    [OrderTypeName] varchar(50)  NOT NULL
);
GO

-- Creating table 'ProductionStatus'
CREATE TABLE [dbo].[ProductionStatus] (
    [ProductionStatusID] int IDENTITY(1,1) NOT NULL,
    [ProductionStatus] varchar(100)  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [AssetID] in table 'Assets'
ALTER TABLE [dbo].[Assets]
ADD CONSTRAINT [PK_Assets]
    PRIMARY KEY CLUSTERED ([AssetID] ASC);
GO

-- Creating primary key on [AssetTypeID] in table 'AssetTypes'
ALTER TABLE [dbo].[AssetTypes]
ADD CONSTRAINT [PK_AssetTypes]
    PRIMARY KEY CLUSTERED ([AssetTypeID] ASC);
GO

-- Creating primary key on [CustomerID] in table 'Customers'
ALTER TABLE [dbo].[Customers]
ADD CONSTRAINT [PK_Customers]
    PRIMARY KEY CLUSTERED ([CustomerID] ASC);
GO

-- Creating primary key on [OrderID] in table 'Orders'
ALTER TABLE [dbo].[Orders]
ADD CONSTRAINT [PK_Orders]
    PRIMARY KEY CLUSTERED ([OrderID] ASC);
GO

-- Creating primary key on [OrderTypeID] in table 'OrderTypes'
ALTER TABLE [dbo].[OrderTypes]
ADD CONSTRAINT [PK_OrderTypes]
    PRIMARY KEY CLUSTERED ([OrderTypeID] ASC);
GO

-- Creating primary key on [ProductionStatusID] in table 'ProductionStatus'
ALTER TABLE [dbo].[ProductionStatus]
ADD CONSTRAINT [PK_ProductionStatus]
    PRIMARY KEY CLUSTERED ([ProductionStatusID] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [AssetTypeID] in table 'Assets'
ALTER TABLE [dbo].[Assets]
ADD CONSTRAINT [FK_Asset_AssetType]
    FOREIGN KEY ([AssetTypeID])
    REFERENCES [dbo].[AssetTypes]
        ([AssetTypeID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Asset_AssetType'
CREATE INDEX [IX_FK_Asset_AssetType]
ON [dbo].[Assets]
    ([AssetTypeID]);
GO

-- Creating foreign key on [AssetID] in table 'Orders'
ALTER TABLE [dbo].[Orders]
ADD CONSTRAINT [FK_Order_Asset]
    FOREIGN KEY ([AssetID])
    REFERENCES [dbo].[Assets]
        ([AssetID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Order_Asset'
CREATE INDEX [IX_FK_Order_Asset]
ON [dbo].[Orders]
    ([AssetID]);
GO

-- Creating foreign key on [CustomerID] in table 'Orders'
ALTER TABLE [dbo].[Orders]
ADD CONSTRAINT [FK_Order_Customer]
    FOREIGN KEY ([CustomerID])
    REFERENCES [dbo].[Customers]
        ([CustomerID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Order_Customer'
CREATE INDEX [IX_FK_Order_Customer]
ON [dbo].[Orders]
    ([CustomerID]);
GO

-- Creating foreign key on [OrderTypeID] in table 'Orders'
ALTER TABLE [dbo].[Orders]
ADD CONSTRAINT [FK_Order_OrderType]
    FOREIGN KEY ([OrderTypeID])
    REFERENCES [dbo].[OrderTypes]
        ([OrderTypeID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Order_OrderType'
CREATE INDEX [IX_FK_Order_OrderType]
ON [dbo].[Orders]
    ([OrderTypeID]);
GO

-- Creating foreign key on [ProductionStatusID] in table 'Orders'
ALTER TABLE [dbo].[Orders]
ADD CONSTRAINT [FK_Order_ProductionStatus]
    FOREIGN KEY ([ProductionStatusID])
    REFERENCES [dbo].[ProductionStatus]
        ([ProductionStatusID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Order_ProductionStatus'
CREATE INDEX [IX_FK_Order_ProductionStatus]
ON [dbo].[Orders]
    ([ProductionStatusID]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------